// 引入express
const express = require('express');
// 连接数据库
const db = require('../utils/database');

const inRouter = express.Router();

// 接口的编写
// 添加
inRouter.post('/stockIn/add',(req,res)=>{

  const sql = `insert into in_stock values('${req.body.in_stock_id}','${req.body.trans_id}','${req.body.house_id}','${req.body.in_stock_quantity}','${req.body.in_stock_date}');`
  
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,msg:'添加入库成功',data:data})
    }
  })
})
inRouter.post('/stockOut/add',(req,res)=>{

  const sql = `insert into out_stock values('${req.body.out_stock_id}','${req.body.rk_id}','${req.body.house_id}','${req.body.out_stock_quantity}','${req.body.out_stock_date}');`

  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,msg:'添加出库成功',data:data})
    }
  })
})

// 删除
inRouter.get('/stockIn/del',(req,res)=>{
  const sql = `DELETE FROM in_stock WHERE in_stock_id = '${req.query.in_stock_id}';`;
  console.log(123);
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      if(!data){
        res.send({error:1,msg:'该商品已经进入出库环节，无法删除该条数据'})
      }else{
        res.send({error:0,msg:'入库数据删除成功',data:data})
      }
    }
  })
})
inRouter.get('/stockOut/del',(req,res)=>{
  const sql = `DELETE FROM out_stock WHERE out_stock_id = '${req.query.out_stock_id}';`;
  console.log(123);
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      if(!data){
        res.send({error:1,msg:'该商品已经进入生产环节，无法删除该条数据'})
      }else{
        res.send({error:0,msg:'出库数据删除成功',data:data})
      }
    }
  })
})

// 修改
inRouter.get('/stockIn/edit',(req,res)=>{
  console.log(req.query.in_stock_quantity);
  const sql = `UPDATE in_stock SET in_stock_quantity = '${req.query.in_stock_quantity}' WHERE in_stock_id = '${req.query.in_stock_id}';`

  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      if(!data){
        res.send({error:1,msg:'该商品已经进入出库环节，无法修改该条数据'})
      }else{
        res.send({error:0,msg:'该条入库数据修改成功',data:data})
      }
    }
  })
})

// 获取/查询:count page chaxun
inRouter.get('/stockIn/get',(req,res)=>{
  const page = req.query.page;
  const count = req.query.count;
  const sql = `select * from in_stock LEFT JOIN transport ON in_stock.trans_id = transport.t_id LEFT JOIN purchase ON transport.t_shopid = purchase.id LEFT JOIN goods ON purchase.p_item = goods.g_id LEFT JOIN class2 ON goods.g_class = class2.c2_id WHERE house_id LIKE '${req.query.house_id}%' AND c2_name LIKE '${req.query.c2_name}%' AND in_stock_date LIKE '${req.query.in_stock_date}%' order by in_stock_id limit ${(page - 1) * count},${count};`;
  const sql2 = `select count(*) as total from in_stock LEFT JOIN transport ON in_stock.trans_id = transport.t_id LEFT JOIN purchase ON transport.t_shopid = purchase.id LEFT JOIN goods ON purchase.p_item = goods.g_id LEFT JOIN class2 ON goods.g_class = class2.c2_id WHERE house_id LIKE '${req.query.house_id}%' AND c2_name LIKE '${req.query.c2_name}%' AND in_stock_date LIKE '${req.query.in_stock_date}%';`;

  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      db.query(sql2,(err2,data2)=>{
        if(err2){
          console.log(err2);
          res.send({error:1,msg:err2})
        }else{
          console.log(data2);
          res.send({error:0,msg:'查询入库成功',data:data,total:data2[0].total})
        }
      })
    }
  })
})
inRouter.get('/stockOut/get',(req,res)=>{
  const page = req.query.page;
  const count = req.query.count;
  const sql = `SELECT * FROM out_stock LEFT JOIN house ON out_stock.house_id = house.house_id LEFT JOIN in_stock ON out_stock.rk_id= in_stock.in_stock_id LEFT JOIN transport ON in_stock.trans_id = transport.t_id LEFT JOIN purchase ON transport.t_shopid = purchase.id LEFT JOIN goods ON purchase.p_item = goods.g_id LEFT JOIN class2 ON goods.g_class = class2.c2_id WHERE out_stock.house_id LIKE '${req.query.house_id}%' AND c2_name LIKE '${req.query.c2_name}%' AND out_stock_date LIKE '${req.query.out_stock_date}%' order by out_stock_id LIMIT ${(page - 1) * count},${count};`;
  const sql2 = `select count(*) as total from out_stock LEFT JOIN house ON out_stock.house_id = house.house_id LEFT JOIN in_stock ON out_stock.rk_id= in_stock.in_stock_id LEFT JOIN transport ON in_stock.trans_id = transport.t_id LEFT JOIN purchase ON transport.t_shopid = purchase.id LEFT JOIN goods ON purchase.p_item = goods.g_id LEFT JOIN class2 ON goods.g_class = class2.c2_id WHERE out_stock.house_id LIKE '${req.query.house_id}%' AND c2_name LIKE '${req.query.c2_name}%' AND out_stock_date LIKE '${req.query.out_stock_date}%';`;
  
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      db.query(sql2,(err2,data2)=>{
        if(err2){
          console.log(err2);
          res.send({error:1,msg:err2})
        }else{
          console.log(data2);
          res.send({error:0,msg:'查询入库成功',data:data,total:data2[0].total})
        }
      })
    }
  })
})

// 销售统计
inRouter.get('/saleStatistical/get',(req,res)=>{
  const sql = [`SELECT class2.c2_name,SUM(数量*销售价) as totala FROM z_sale
  LEFT JOIN production ON z_sale.p_id = production.p_id
  LEFT JOIN out_stock ON production.p_ck_id = out_stock.out_stock_id
  LEFT JOIN in_stock ON out_stock.rk_id = in_stock.in_stock_id
  LEFT JOIN transport ON in_stock.trans_id = transport.t_id
  LEFT JOIN purchase ON transport.t_shopid = purchase.id
  LEFT JOIN goods ON purchase.p_item = goods.g_id
  LEFT JOIN class2 ON goods.g_class = class2.c2_id
  LEFT JOIN class1 ON class2.class1_id = class1.c1_id
  GROUP BY class2.c2_name;`,
      `SELECT 销售方式 as type,COUNT(销售方式) as totalb FROM z_sale GROUP BY 销售方式;`
  ]

  db.query(sql[0], function (err, data) {
      if (err) {
          res.send({ code: 500, err: err })
      } else {
          db.query(sql[1], function (err2, data2) {
              if (err2) {
                  res.send({ code: 300, err2: err2 })
              } else {
                // console.log(data2[0].totalb);
                // console.log(data2[1]);
                // console.log(data2[1].type);
                  res.send({ code: 200, data: [data, data2] })
              }
          })

      }
  })
})
inRouter.get('/statistic/place',(req,res)=>{
  const sql =  `SELECT class2.c2_name as lei,COUNT(数量) as num FROM z_sale
  LEFT JOIN production ON z_sale.p_id = production.p_id
  LEFT JOIN out_stock ON production.p_ck_id = out_stock.out_stock_id
  LEFT JOIN in_stock ON out_stock.rk_id = in_stock.in_stock_id
  LEFT JOIN transport ON in_stock.trans_id = transport.t_id
  LEFT JOIN purchase ON transport.t_shopid = purchase.id
  LEFT JOIN goods ON purchase.p_item = goods.g_id
  LEFT JOIN class2 ON goods.g_class = class2.c2_id
  LEFT JOIN class1 ON class2.class1_id = class1.c1_id
  GROUP BY class2.c2_name;`

  db.query(sql, function (err, data) {
    if (err) {
        res.send({ code: 501, err: err })
    } else {
      console.log(data[0].num);
      res.send({ code: 301, data: data})
    }
})
})
module.exports = inRouter;